package cn.lili.modules.order.order.mapper;

import cn.lili.modules.member.entity.dto.CityDistributionDTO;
import cn.lili.modules.order.order.entity.dos.Order;
import cn.lili.modules.order.order.entity.dto.OrderDTO;
import cn.lili.modules.order.order.entity.dto.OrderExportDTO;
import cn.lili.modules.order.order.entity.dto.OrderFlowQueryDTO;
import cn.lili.modules.order.order.entity.vo.*;
import cn.lili.modules.payment.entity.dos.PaymentLog;
import cn.lili.modules.store.entity.vos.StoreSaleTopVO;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 订单数据处理层
 *
 * @author Chopper
 * @since 2020/11/17 7:35 下午
 */
public interface OrderMapper extends BaseMapper<Order> {

    /**
     * 修改订单状态
     *
     * @param status  状态
     * @param orderSn 订单编号
     */
    @Update({"update li_order set order_status = #{status} where sn = #{orderSn}"})
    void updateStatus(String status, String orderSn);

    /**
     * 查询订单简短信息分页
     *
     * @param page         分页
     * @param queryWrapper 查询条件
     * @return 简短订单分页
     */
    @Select("select o.sn,o.trade_sn,o.flow_price,o.create_time,o.order_status,o.pay_status,o.payment_method,o.payment_time," +
            "o.nickname,o.store_name as store_name,o.store_id as store_id,o.client_type,o.order_type,o.deliver_status," +
            "o.order_promotion_type, o.price_detail, o.is_duty_free ,o.is_cancel AS isNotAllowCancel" +
            ",o.supply_chain_state,o.mh_id,o.mh_name,o.recycle_status,o.id,o.buyer_id,o.out_trade_no " +
            ",GROUP_CONCAT(oi.goods_type) as group_goods_type" +
            ",GROUP_CONCAT(oi.goods_id) as group_goods_id," +
            " GROUP_CONCAT(oi.sku_id) as group_sku_id," +
            " GROUP_CONCAT(oi.num) as group_num" +
            ",GROUP_CONCAT(oi.image) as group_images" +
            ",GROUP_CONCAT(oi.goods_name) as group_name " +
            ",GROUP_CONCAT(oi.after_sale_status) as group_after_sale_status" +
            ",GROUP_CONCAT(oi.complain_status) as group_complain_status" +
            ",GROUP_CONCAT(oi.comment_status) as group_comment_status" +
            ",GROUP_CONCAT(oi.sn) as group_order_items_sn " +
            ",GROUP_CONCAT(oi.goods_price) as group_goods_price " +
            ",GROUP_CONCAT(DISTINCT oi.pack_name SEPARATOR ', ') AS packNames" +
            ",GROUP_CONCAT(DISTINCT oi.pack_id SEPARATOR ', ') AS packIds "+
            " FROM li_order o INNER JOIN li_order_item AS oi on o.sn = oi.order_sn ${ew.customSqlSegment} ")
    Page<OrderSimpleVO> queryByParams(Page<OrderSimpleVO> page, @Param(Constants.WRAPPER) Wrapper<OrderSimpleVO> queryWrapper);

    /**
     * 查询导出订单DTO列表
     *
     * @param queryWrapper 查询条件
     * @return 导出订单DTO列表
     */
    @Select("SELECT o.sn,o.mh_id,o.mh_name,o.create_time,o.nickname as memberName,o.consignee_name,o.consignee_mobile,o.consignee_address_path,o.consignee_detail,o.out_trade_no," +
            "o.payment_method,o.client_type, o.logistics_name,o.freight_price,oi.goods_price,o.discount_price,o.flow_price,oi.goods_name,oi.num,oi.goods_id, " +
            "oi.cost_price as costPrice, oi.sku_id as skuId, " +
            "o.remark,o.order_status,o.pay_status,o.deliver_status,o.need_receipt,o.store_name, oi.price_detail as priceDetail FROM li_order o LEFT JOIN li_order_item oi " +
            "ON oi.order_sn=o.sn ${ew.customSqlSegment}")
    List<OrderExportDTO> queryExportOrder(@Param(Constants.WRAPPER) Wrapper<OrderSimpleVO> queryWrapper);


    @Select("SELECT lbro.order_sn as sn,lbro.rule_goods_name as sendGoods, lbro.rule_goods_image as sendGoodsImg, " +
            "count(lbro.rule_goods_name) as sendGoodsNum " +
            "FROM li_order o LEFT JOIN li_order_item oi " +
            "ON oi.order_sn=o.sn LEFT JOIN lilishop_promotion.li_blindbox_rule_order lbro on lbro.order_sn=o.sn " +
            "${ew.customSqlSegment}" + " group by lbro.order_sn, lbro.rule_goods_name, lbro.rule_goods_name, lbro.rule_goods_image")
    List<OrderExportDTO> queryExportOrderByWaitShip(@Param(Constants.WRAPPER) Wrapper<OrderSimpleVO> queryWrapper);

    /**
     * 查询导出待发货订单DTO列表
     *
     * @param queryWrapper 查询条件
     * @return 导出订单DTO列表
     */
    @Select("SELECT o.sn,o.create_time,o.nickname as memberName,o.consignee_name,o.consignee_mobile,o.consignee_address_path,o.consignee_detail," +
            "o.payment_method,o.client_type, o.logistics_name,o.freight_price,o.goods_price,o.discount_price,o.flow_price,oi.goods_name,oi.num,oi.goods_id," +
            "o.remark,o.order_status,o.pay_status,o.deliver_status,o.need_receipt,o.store_name " +
            "FROM li_order o LEFT JOIN li_order_item oi " +
            "ON oi.order_sn=o.sn " +
            "${ew.customSqlSegment}")
    List<OrderExportDTO> queryExportOrderByWaitShip2(@Param(Constants.WRAPPER) Wrapper<OrderSimpleVO> queryWrapper);


    /**
     * 查询订单支付记录
     *
     * @param page         分页
     * @param queryWrapper 查询条件
     * @return 订单支付记录分页
     */
    @Select("select o.* from li_order o ${ew.customSqlSegment} ")
    Page<PaymentLog> queryPaymentLogs(Page<PaymentLog> page, @Param(Constants.WRAPPER) Wrapper<PaymentLog> queryWrapper);

    /**
     * 查询订单信息
     *
     * @param queryWrapper 查询条件
     * @return 简短订单分页
     */
    @Select("select o.* " +
            " FROM li_order o INNER JOIN li_order_item AS oi on o.sn = oi.order_sn ${ew.customSqlSegment} ")
    List<Order> queryListByParams(@Param(Constants.WRAPPER) Wrapper<Order> queryWrapper);

    /**
     * 查询订单信息
     *
     * @param orderPromotionType 订单促销类型
     * @param payStatus          支付状态
     * @param parentOrderSn      父订单编号
     * @param promotionId        促销活动ID
     * @param skuId              skuId
     * @return 简短订单分页
     */
    @Select("select o.* " +
            " FROM li_order o INNER JOIN li_order_item AS oi on o.sn = oi.order_sn where o.order_promotion_type = #{orderPromotionType} and o" +
            ".pay_status = #{payStatus} and o.parent_order_sn = #{parentOrderSn} and o.promotion_id = #{promotionId} and oi.sku_id = #{skuId} ")
    List<Order> queryListByPromotionId(String orderPromotionType, String payStatus, String parentOrderSn, String promotionId, String skuId);



    @Select("select count(*) saleCount, ROUND(count(*) / (select count(*) from li_order ${ew.customSqlSegment}) * 100, 2) as percent, store_id " +
            "from li_order ${ew.customSqlSegment} GROUP BY store_id ORDER BY saleCount desc")
    Page<StoreSaleTopVO> getStoreSaleRank (Page<StoreSaleTopVO> page, @Param(Constants.WRAPPER) Wrapper<Order> queryWrapper);

    /**
     * 根据区域查询订单列表
     * @param page
     * @param dto
     * @return
     */
    Page<OrderItemViewVO> getCityDistributionComissList(Page page, @Param("dto") CityDistributionDTO dto);

    /**
     * 统计 已结算、未结算、已退单、合计金额
     * @param dto
     * @return
     */
    Map<String, BigDecimal> getCityDistributionSumComiss(@Param("dto") CityDistributionDTO dto);


    Map<String, BigDecimal> getCityDistributionSumGreenScore(@Param("dto") OrderFlowQueryDTO dto);

    /**
     * 定时任务城主分佣统计
     * @param
     * @return
     */
    List<OrderItemViewVO> getCityDistributionSumComissByTaskTime(Page page, @Param("joinAddress") String joinAddress, @Param("startLocalDate") LocalDate startLocalDate, @Param("endLocalDate") LocalDate endLocalDate);

    List<OrderItemViewVO> getUnionSumComissByTaskTime(Page page, @Param("joinAddress") String joinAddress, @Param("startLocalDate") LocalDate startLocalDate, @Param("endLocalDate") LocalDate endLocalDate);

    /**
     * 返回上个月，有哪些区域订单
     * @param begin 上个与开始时间
     * @param end 上个月结束时间
     * @return 有哪些区域订单
     */
    List<String> executeCityDistributionLastMonthOrder(@Param("begin") LocalDateTime begin, @Param("end") LocalDateTime end, @Param("subDistributionCount") Integer subDistributionCount);


    @Select("select DATE_FORMAT(create_time, '%Y/%m' ) as yearMonth " +
            "from li_order o " +
            "join li_order_flow lof on o.sn = lof.order_sn " +
            "where o.consignee_address_id_path like concat(#{regionId},'%') " +
            "and lof.union_green_score > 0 " +
            "group by o.yearMonth " +
            "order by o.yearMonth desc")
    Page<UnionMonthFundsVO> getOrderMonthByRegion (Page page, @Param("regionId") String regionId);

    @Select("select sn from li_order o " +
            "join li_order_flow lof on o.sn = lof.order_sn " +
            "where o.consignee_address_id_path like concat(#{regionId},'%') " +
            "and lof.union_green_score > 0 " +
            "and DATE_FORMAT(o.create_time, '%Y/%m' ) = #{month}")
    List<String> getOrderSnByMonth (@Param("regionId") String regionId,@Param("month") String month);


    /**
     * 是否存在未完成的订单
     * @param joinAddress
     * @param startLocalDate
     * @param endLocalDate
     * @return
     */
    @Select(" SELECT count(1) FROM li_order AS o " +
            " JOIN li_order_flow AS ofl ON o.sn = ofl.order_sn " +
            " WHERE ofl.profit_sharing in (" + "'WAIT_COMPLETE'," + "'PROCESSING'," + "'FAIL'," + "'ARTIFICIAL'" + ") " +
            " AND o.consignee_address_id_path LIKE CONCAT(#{joinAddress},'%') " +
            " AND o.create_time >= #{startLocalDate} AND o.create_time < #{endLocalDate}  AND ofl.castellan_green_score > 0 ")
    Integer isExistsUnCompletedOrder(String joinAddress, LocalDate startLocalDate, LocalDate endLocalDate);

    @Select(" SELECT count(1) FROM li_order_flow AS lof " +
            " JOIN li_order AS o ON lof.order_sn = o.sn " +
            " WHERE lof.profit_sharing in ('WAIT_COMPLETE','PROCESSING') " +
            " AND lof.union_green_score > 0 " +
            " AND o.consignee_address_id_path LIKE CONCAT(#{joinAddress},'%') " +
            " AND lof.create_time >= #{startLocalDate} AND lof.create_time < #{endLocalDate}")
    Integer isExistsUnCompletedOrderByOc(String joinAddress, LocalDate startLocalDate, LocalDate endLocalDate);

    /**
     * 运营中心-订单列表
     * @param vo
     * @return
     */
    Page<OrderViewVO> getOperationCenterOrderList(Page page, @Param("vo") OrderViewVO vo);

    /**
     * 统计订单总数
     * @param vo
     * @return
     */
    Integer operationCenterOrderStatistics(OrderViewVO vo);


    /**
     * 获取礼包订单列表
     * @return
     */
    Page<OrderVO> getPackGiftOrderList(Page page, @Param("dto") OrderDTO dto);


    /**
     * 根据时间和身份证号统计免税商品的交易额
     * @param dto
     * @return
     */
    @Select("SELECT IFNULL(sum(flow_price),0) FROM li_order \n" +
            "WHERE is_duty_free = 1 AND buyer_card_no = #{buyerCardNo} \n" +
            "AND create_time >= #{startTime}\n" +
            "AND create_time <= #{endTime} AND order_status IN (" +
            "'PAID','UNDELIVERED','DELIVERED', 'OVERVIEW','COMPLETED','STAY_PICKED_UP')")
    Double sumTotalFreeTaxOrder(OrderDTO dto);
}